﻿-- 20120801 新增字段


-- =============================================
-- Author:		<Author,@XFL>
-- Create date: <Create Date,2012 03 06 >
-- Description:	<Description,用于工资汇总,使用高级搜索时使用>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Wage_Getlist_Gather_MoreCompany]
	(
		@Month_Start nvarchar(7),
		@Month_End nvarchar(7),		
		@CompanyId nvarchar(500),
		@DepId int,
		@GradeId int,
		@SerchType int 
	)
AS
BEGIN

	--	将公司ID串 插入临时表中
		declare @Companyid_T table(id int identity(1,1),cid int)
		declare @CompanyID_S nvarchar(500)
		set @CompanyID_S=@CompanyId+','
		print @CompanyID_S
		Declare @i int
		Set @i=0
		while(@i<len(@CompanyID_S))
		begin 
		Insert Into @Companyid_T(cid) Values(substring(@CompanyID_S,@i,charindex(',',@CompanyID_S,@i)-@i))
		Set @i=charindex(',',@CompanyID_S,@i)+1
		end
		
	--	select * from Company  where id in (select cid from @Companyid_T)

		-- 执行 查询全部单位的汇总，注意此时传入的 公司ID 为 6,22,50 格式

		declare @list_table_1 table(
		id int identity(1,1)
		,CompanyId int
		,BasicWage Decimal
		,PostWage Decimal
		,WorkWage Decimal
		,JxWage Decimal
		,ManQin Decimal
		,Allowance Decimal
		,FixedAllowance Decimal
		,QtAllowance Decimal
		,TiCheng Decimal
		,MTiCheng Decimal
		,YjBonus Decimal
		,LsBonus Decimal
		,ChuQinKouKuan Decimal
		,BxKouKuan Decimal
		,ChiDaoKouKuan Decimal
		,YJKouKuan Decimal
		,OtherKouKuan Decimal
		,Count_Sum int
		--
		,XSTC DECIMAL
		,XFTC DECIMAL
		,BLTC DECIMAL
		,LSwance DECIMAL
		,ZBwance DECIMAL
		,GRSDSKouKuan DECIMAL
		,GJJKouKuan DECIMAL
		,YFHeJi  DECIMAL
		--
		)

		;WITH list As(
				Select 
				CompanyId
				,BasicWage
				,PostWage
				,WorkWage
				,JxWage
				,ManQin
				,Allowance
				,FixedAllowance
				,QtAllowance
				,TiCheng
				,MTiCheng
				,YjBonus
				,LsBonus
				,ChuQinKouKuan
				,BxKouKuan
				,ChiDaoKouKuan
				,YJKouKuan
				,OtherKouKuan,isnull(Deduction,0) as Deduction
				,FaFangMonth
				,1 as Count_Sum
				--
				,XSTC
				,XFTC
				,BLTC
				,LSwance
				,ZBwance
				,GRSDSKouKuan
				,GJJKouKuan
				,YFHeJi 
				--
				From Employee_Wage  
				Where CompanyId in (select C.cid from @Companyid_T C) and (@GradeId=0 Or GradeId=@GradeId  ) and FinanceFlag=2
		 and(  Convert(nchar(7),FaFangMonth,120)>=@Month_Start and  Convert(nchar(7),FaFangMonth,120) <=@Month_End)

		) 
		insert into @list_table_1(
		CompanyId	
		,BasicWage
		,PostWage
		,WorkWage
		,JxWage
		,ManQin
		,Allowance
		,FixedAllowance
		,QtAllowance
		,TiCheng
		,MTiCheng
		,YjBonus
		,LsBonus
		,ChuQinKouKuan
		,BxKouKuan
		,ChiDaoKouKuan
		,YJKouKuan
		,OtherKouKuan
		,Count_Sum
		--
		,XSTC
		,XFTC
		,BLTC
		,LSwance
		,ZBwance
		,GRSDSKouKuan
		,GJJKouKuan
		,YFHeJi 
		--
		) 
		select 
		CompanyId
		,BasicWage
		,PostWage
		,WorkWage
		,JxWage
		,ManQin
		,Allowance
		,FixedAllowance
		,QtAllowance
		,TiCheng
		,MTiCheng
		,YjBonus
		,LsBonus
		,ChuQinKouKuan
		,BxKouKuan
		,ChiDaoKouKuan
		,YJKouKuan
		,OtherKouKuan+Deduction
		,Count_Sum
		--
		,XSTC
		,XFTC
		,BLTC
		,LSwance
		,ZBwance
		,GRSDSKouKuan
		,GJJKouKuan,YFHeJi 
		--
		From list  ;
		-- 上面完成 list 表
			--Select * from @list_table_1 
		;WITH list_Union As 
		(
		Select 
				CompanyId
				,sum (BasicWage)  as BasicWage_s
				,sum (PostWage)  as PostWage_s
				,sum (WorkWage)  as WorkWage_s
				,sum (JxWage)  as JxWage_s
				,sum (ManQin)  as ManQin_s
				,sum (Allowance)  as Allowance_s
				,sum (FixedAllowance)  as FAllowance_s
				,sum (QtAllowance)  as QtAllowance_s
				,sum (TiCheng)  as TiCheng_s
				,sum (MTiCheng)  as MTiCheng_s
				,sum (YjBonus)  as YjBonus_s
				,sum (LsBonus)  as LsBonus_s
				,sum (ChuQinKouKuan)  as ChuQinKouKuan_s
				,sum (BxKouKuan)  as BxKouKuan_s
				,sum (ChiDaoKouKuan)  as ChiDaoKouKuan_s
				,sum (YJKouKuan)  as YJKouKuan_s
				,sum (OtherKouKuan)  as OtherKouKuan_s
				,sum (Count_Sum) as Count_Sum_s
				--
				,SUM(XSTC) AS XSTC_s
				,SUM(XFTC) AS XFTC_s
				,SUM(BLTC) AS BLTC_s
				,SUM(LSwance) AS LSwance_s
				,SUM(ZBwance) AS ZBwance_s
				,SUM(GRSDSKouKuan) AS GRSDSKouKuan_s
				,SUM(GJJKouKuan) AS GJJKouKuan_s
				,sum(YFHeJi) as YFHeJi_s
				--
				From @list_table_1
				group by CompanyId

		UNION all

		Select 	
				C.cid as CompanyId 
				,0  as BasicWage_s
				,0  as PostWage_s
				,0  as WorkWage_s
				,0  as JxWage_s
				,0  as ManQin_s
				,0  as Allowance_s
				,0  as FAllowance_s
				,0  as QtAllowance_s
				,0  as TiCheng_s
				,0  as MTiCheng_s
				,0  as YjBonus_s
				,0  as LsBonus_s
				,0  as ChuQinKouKuan_s
				,0  as BxKouKuan_s
				,0  as ChiDaoKouKuan_s
				,0  as YJKouKuan_s
				,0  as OtherKouKuan_s
				,0  as Count_Sum_s
				--
				,0  AS XSTC_s
				,0  AS XFTC_s
				,0  AS BLTC_s
				,0  AS LSwance_s
				,0  AS ZBwance_s
				,0  AS GRSDSKouKuan_s
				,0  AS GJJKouKuan_s
				,0 as YFHeJi_s
				--
		From @Companyid_T  C
		where 
		Cid not in 
		(
		Select CompanyId From Employee_Wage	where   FinanceFlag=2  and(  Convert(nchar(7),FaFangMonth,120)>=@Month_Start and  Convert(nchar(7),FaFangMonth,120) <=@Month_End) and (@GradeId=0 or GradeId=@GradeId) group by  CompanyId 
		)

		)

		select *
		,(Select CompanyName From Company Where Id=list_Union.CompanyId) As CompanyName
		,'00' AS bmbh --补充字段
		,'00' AS [DName]  --补充字段
		 from list_Union order by CompanyId
		
		--执行 查询全部单位的汇总 结束

End
	RETURN
